package persistence.database;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import core.exception.SaveException;
import persistence.ContainerDTO;
import persistence.DataPersistor;
import persistence.ProductContainerDAO;
import persistence.ProductDTO;

/**
 * ProductContainerDAO for database persistence
 * @author Sabrina
 *
 */
public class DBProductContainerDAO implements ProductContainerDAO {
	private DataPersistor _DP;
	private DBTransactionManager _TM;
	
	/**
	 * Construct a DBProductContainerDAO
	 */
	public DBProductContainerDAO() {
		_DP = DataPersistor.instance();
		_TM = (DBTransactionManager)_DP.getTransactionManager();
	}
	/**
	 * @inheritDoc
	 */
	@Override
	public void addRelationship(ProductDTO product, ContainerDTO container) throws SaveException {
		String productID = product.getProductBarcode();
		int containerID = container.getContainerID();
		String sql = "INSERT INTO ProductInContainer (ProductID, ContainerID) VALUES (?, ?);";
		PreparedStatement stmt = null;
		
		if(relationshipExists(productID, containerID)) {
			System.err.println("That relationship already exists! [" + productID + "=>" 
						+ containerID + "]");
			return;
		}
		
		try {
			stmt = _TM.getConnection().prepareStatement(sql);
			stmt.setString(1, productID);
			stmt.setInt(2, containerID);
			int rowsAffected = stmt.executeUpdate();
			if (rowsAffected != 1) {
				throw new SaveException("Couldn't add relationship!");
			}
		}
		catch (SQLException e) {
			e.printStackTrace();
			throw new SaveException(e.getMessage());
		}
		finally {
			try {
				if(stmt != null) stmt.close();
			}
			catch (SQLException e) {
				//ignore
			}
		}
	}
	
	private boolean relationshipExists(String pid, int cid) throws SaveException {
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String sql = "SELECT * FROM ProductInContainer WHERE ProductID=? AND ContainerID=?;";
		try {
			stmt = _TM.getConnection().prepareStatement(sql);
			stmt.setString(1, pid);
			stmt.setInt(2, cid);
			rs = stmt.executeQuery();
			if (rs.next()) {
				return true;
			}
			else {
				return false;
			}
		}
		catch (SQLException e) {
			throw new SaveException(e.getMessage());
		}
		finally {
			try {
				if(stmt != null) stmt.close();
				if(rs != null) rs.close();
			}
			catch (SQLException e) {
				//ignore
			}
		}
	}

	/**
	 * @inheritDoc
	 */
	@Override
	public void removeRelationship(ProductDTO product, ContainerDTO container) 
			throws SaveException {
		String productID = product.getProductBarcode();
		int containerID = container.getContainerID();		
		String sql = "DELETE FROM ProductInContainer WHERE ProductID=? AND ContainerID=?;";
		PreparedStatement stmt = null;
		
		try {
			stmt = _TM.getConnection().prepareStatement(sql);
			stmt.setString(1, productID);
			stmt.setInt(2, containerID);
			int rowsAffected = stmt.executeUpdate();
			if (rowsAffected != 1) {
				throw new SaveException("Couldn't delete relationship!");
			}
		} 
		catch (SQLException e) {
			e.printStackTrace();
			throw new SaveException(e.getMessage());
		}
		finally {
			try {
				if(stmt != null) stmt.close();
			}
			catch (SQLException e) {
				//ignore
			}
		}
	}

}
